﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataClassLayer
{
    public class CoachCollection
    {
        public CoachCollection()
        {
            
        }
        public bool Add(Coach coach)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Insert into SOCCER_COATCHES (Name,DateOfBirth,Nationality,GeneralInformation,Image) Values
                   ('" + coach.Name + "',Convert(datetime,'"
                   + coach.DateOfBirth + "',103),"
                   + coach.Nationality + ",'"
                   + coach.GeneralInformation + "','"
                   + coach.Image + "')";
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Edit(Coach coach)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Update SOCCER_COATCHES Set 
                     Name='" + coach.Name +
                     "',DateOfBirth=Convert(datetime,'" + coach.DateOfBirth +
                     "',103),Nationality=" + coach.Nationality  +
                     ",GeneralInformation='" + coach.GeneralInformation +
                     ",Image='" + coach.Image +
                     "' Where ID=" + coach.ID;
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public bool Delete(Coach coach)
        {
            try
            {
                List<string> query = new List<string>();
                string command = @"Delete From SOCCER_COATCHES Where ID= " + coach.ID; 
                query.Add(command);
                DataAccessObject.DataBase.CreateUpdateDelete(query);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public DataTable FillList(Coach coach)
        {
            try
            {
                string wherestatement = WhereStatement(coach);
                string command = @"SELECT        SOCCER_COATCHES.ID, SOCCER_COATCHES.Name, SOCCER_COATCHES.DateOfBirth, SOCCER_COATCHES.Nationality, SOCCER_COUNTRY.Description AS NationalityName,SOCCER_COATCHES.GeneralInformation, SOCCER_COATCHES.Image
                         FROM            SOCCER_COATCHES INNER JOIN
                         SOCCER_COUNTRY ON SOCCER_COATCHES.Nationality = SOCCER_COUNTRY.ID";
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    command += " Where " + wherestatement;
                }
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                return table;
            }
            catch
            {
                return null;
            }
        }
        public int MaxID()
        {
            try
            {
                int Maxid = 1;
                string command = @"SELECT    Max(ID) AS MaxID FROM   SOCCER_COATCHES";
                DataTable table = DataAccessObject.DataBase.ExcuteQuery(command);
                if (table.Rows.Count == 1)
                {
                    Maxid = int.Parse(table.Rows[0]["MaxID"].ToString());
                }
                return Maxid;
            }
            catch
            {
                return 1;
            }
        }
        public string WhereStatement(Coach coach)
        {
            string wherestatement = "";
            if (coach.ID > 0)
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_COATCHES.ID= " + coach.ID;
                }
                else
                {
                    wherestatement += "SOCCER_COATCHES.ID= " + coach.ID;
                }
            }
            if (coach.Name != null &&
                coach.Name != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_COATCHES.Name Like '%" + coach.Name + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_COATCHES.Name Like '%" + coach.Name + "%' ";
                }
            }
            if (coach.NationalityName != null &&
                coach.NationalityName != "")
            {
                if (wherestatement != null &&
                    wherestatement != "")
                {
                    wherestatement += "And SOCCER_COUNTRY.Description Like '%" + coach.NationalityName + "%' ";
                }
                else
                {
                    wherestatement += " SOCCER_COUNTRY.Description Like '%" + coach.NationalityName + "%' ";
                }
            }
            return wherestatement;
        }
    }
}
